package com.lsf.studentmanagerup.dao;

import com.lsf.studentmanagerup.bean.Student;
import com.lsf.studentmanagerup.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author 李树飞
 */
public class StudentDaoImp implements StudentDao{
    /**
     * 增加学生
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public int addStudent(Student student) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "insert into student values(?,?,?,?,?,?,?)";
        //参数
        Object[] params = {student.getId(), student.getName(), student.getGender(),
                student.getBirthday(), student.getEmail(), student.getComment(), student.getCid()};
        //返回结果
        return queryRunner.update(sql, params);
    }

    /**
     * 删除学生
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public int removeStudentById(int id) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "delete from student where id = ?";
        //返回结果
        return queryRunner.update(sql, id);
    }

    /**
     * 根据学号修改学生
     * @param student
     * @return
     * @throws SQLException
     */
    @Override
    public int updateStudentById(Student student) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "update student set name = ?, gender = ?, birthday = ?, email = ?, comment = ?, cid = ? where id = ?";
        //参数
        Object[] params = {student.getName(), student.getGender(), student.getBirthday(),
                student.getEmail(), student.getComment(), student.getCid(), student.getId()};
        //返回结果
        return queryRunner.update(sql, params);
    }

    /**
     * 根据学号查找学生
     * @param id
     * @return
     * @throws SQLException
     */
    @Override
    public Student getStudentById(String id) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "select * from student where id = ?";
        //返回学生对象
        return queryRunner.query(sql, new BeanHandler<>(Student.class), id);
    }

    /**
     * 获取所有学生信息
     * @return
     * @throws SQLException
     */
    @Override
    public List<Student> getAllStudents() throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句
        String sql = "select * from student";
        //返回学生对象
        return queryRunner.query(sql, new BeanListHandler<>(Student.class));
    }

    /**
     * 根据查询条件，查找总记录数
     * @param condition
     * @return
     * @throws SQLException
     */
    @Override
    public long getTotalCount(Map<String, String> condition) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句，条件可能为空时，为了有where，写where1=1
        StringBuilder sql = new StringBuilder("select count(*) from student where 1 = 1");
        List<Object> params = new ArrayList<>();
        //遍历condition，组成查询条件拼接到sql
        for (Map.Entry<String, String> entry : condition.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            //如果查找条件不为空则拼接起来
            if ((null != value && !("".equals(value)))) {
                sql.append(" and ").append(key).append(" like concat('%', ?, '%')");
                params.add(value);
            }
        }
        System.out.println(sql.toString());
        System.out.println(params);

        //返回结果
        return queryRunner.query(sql.toString(), new ScalarHandler<>(), params.toArray());
    }

    /**
     * 分页查询
     * @param start     起始页，从0开始
     * @param rows      每页记录数
     * @param condition 查询条件
     * @return
     * @throws SQLException
     */
    @Override
    public List<Student> getStudentsByPage(int start, int rows, Map<String, String> condition) throws SQLException {
        //创建qr对象
        QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
        //sql语句，条件可能为空时，为了有where，写where1=1
        StringBuilder sql = new StringBuilder("select * from student where 1 = 1");
        List<Object> params = new ArrayList<>();
        //遍历condition，组成查询条件拼接到sql
        for (Map.Entry<String, String> entry : condition.entrySet()) {
            String key = entry.getKey();
            String value = entry.getValue();
            if (null != value && !("".equals(value))) {
                sql.append(" and ").append(key).append(" like concat('%', ?, '%')");
                params.add(value);
            }
        }
        //拼接分页条件到sql后面
        sql.append(" limit ?, ?");
        params.add(start);
        params.add(rows);
        System.out.println(sql.toString());
        System.out.println(params);

        //返回结果
        return queryRunner.query(sql.toString(), new BeanListHandler<>(Student.class), params.toArray());
    }
}
